package com.xiaoshuidi.cloud.module.iot.dal.mapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xiaoshuidi.cloud.module.iot.api.device.vo.PullDeviceApiVo;
import com.xiaoshuidi.cloud.module.iot.controller.admin.doorlock.vo.PullDeviceVo;
import com.xiaoshuidi.cloud.module.iot.api.device.vo.WaterMeterCommonResponseModel;
import com.xiaoshuidi.cloud.module.iot.dal.pojo.TbDeviceDo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

/**
 * @author Sky
 * create 2019/04/16
 * email sky.li@ixiaoshuidi.com
 **/
@Mapper
public interface DeviceDoMapper extends BaseMapper<TbDeviceDo> {
    /**
     * 据水滴api_no列表拉取设备列表
     *
     * @param apiNos
     * @return
     */
    @Select("SELECT device_no,device_name,device_type,brand_no,api_no FROM tb_device WHERE status=1 " +
            "        AND api_no IN " +
            "        <foreach collection='apiNos'item='apiNo' index='index' open='(' close=')' separator=','> " +
            "            #{apiNo} " +
            "        </foreach>")
    List<PullDeviceVo> getDeviceListByApiNo(@Param("apiNos") List<String> apiNos);

    /**
     * 查询设备 断电或无更新
     * @param deviceType
     * @param time
     * @return
     */
    @Select({"select device_no,device_name,device_type,brand_no,api_no from tb_device where device_type = #{deviceType} and (power_status = 0 " +
            "or date_format(report_time,'%Y-%m-%d %H:%i:%s') <= #{time})"})
    List<PullDeviceVo> queryDeviceState(@Param("deviceType") String deviceType, @Param("time") String time);

    @Select({"select device_no,create_time FROM tb_device_operation WHERE status=1 and op_type=1 " +
            "and date_format(create_time,'%Y-%m-%d') > #{time}  GROUP BY device_no ORDER BY create_time desc"})
    List<PullDeviceVo> queryNotOpenDoor(@Param("time") String time);

    @Select({"SELECT t1.device_no deviceNo, count(t1.device_no) onlineStatus from (select device_no,date_format(create_time,'%Y-%m-%d') time " +
            "FROM tb_device_operation WHERE status=2 and op_type=1 and date_format(create_time,'%Y-%m-%d') > #{time} GROUP BY device_no,time) t1"})
    List<PullDeviceVo> queryOpenDoor(@Param("time") String time);

    @Select({"SELECT sum(device_no) onlineStatus,device_no FROM tb_device_operation WHERE status=1 and op_type=1 " +
            "and date_format(create_time,'%Y-%m-%d') = #{time} GROUP BY device_no "})
    List<PullDeviceVo> queryOpenNum(@Param("time") String time);


    /**
     * 批量删除设备
     *
     * @param deletedDeviceList
     * @return
     */
    @Update("UPDATE tb_device SET status=0 WHERE status=1 AND device_third_no IN " +
            "        <foreach collection= 'deletedDeviceList' item= 'device' index= 'index' open= '(' close= ')' separator= ','> " +
            "            #{device.deviceThirdNo} " +
            "        </foreach>")
    int deleteDeviceList(@Param("deletedDeviceList") List<TbDeviceDo> deletedDeviceList);

    /**
     * 根据device_name模糊查询设备列表（品类）
     *
     * @param wrapper
     * @return
     */
    @Select(" SELECT device_no,device_name,device_type,brand_no,api_no,online_status,power_status,battery_level,report_time  " +
            "        FROM tb_device " +
            " where ${ew.sqlSegment}")
    List<PullDeviceVo> getDevcieListByDeviceName(@Param("ew") QueryWrapper<?> wrapper);

    /**
     * 根据device_no 列表批量查询设备列表
     *
     * @param wrapper
     * @return
     */
    @Select("SELECT device_no,device_name,device_type,brand_no,api_no,online_status,power_status,battery_level,report_time,current_reading  " +
            "        FROM tb_device " +
            " where ${ew.sqlSegment}")
    List<PullDeviceVo> getDeviceListByDeviceNos(@Param("ew") QueryWrapper<?> wrapper);

    /**
     * 批量清零设备剩余金额
     *
     * @param deviceNos
     * @return
     */
    @Update("UPDATE tb_device SET update_time=NOW() WHERE status=1 AND device_no IN " +
            "        <foreach collection= 'deviceNos' item= 'deviceNo' index= 'index' open= '(' close= ')' separator= ','> " +
            "            #{deviceNo}" +
            "        </foreach>")
    int updateRemainingAmountClear(@Param("deviceNos") List<String> deviceNos);

    /**
     * 根据device_no 列表批量分页查询设备列表
     *
     * @return
     */
    @Select("  SELECT distinct(td.id),device_no,device_third_no,device_name,device_type,brand_no,api_no,online_status,power_status,battery_level,report_time,current_reading " +
            "        FROM tb_device td " +
            " where ${ew.sqlSegment}")
    List<PullDeviceApiVo> findDeviceListByDeviceNosPage(@Param("ew") QueryWrapper<?> wrapper);

    /**
     * 根据device_no 列表批量分页查询设备列表
     *
     * @return
     */
    @Select("  SELECT distinct(td.id),device_no,device_third_no,device_name,device_type,brand_no,api_no,online_status,power_status,battery_level,report_time,current_reading  " +
            "        FROM tb_device td " +
            " where ${ew.sqlSegment}")
    List<PullDeviceVo> findDeviceListByDeviceNos(@Param("ew") QueryWrapper<?> wrapper);

    @Select(" select td.api_no apiNo,td.brand_no brandNo,td.device_no deviceNo,td.device_name deviceName,td.device_model deviceModel,td.online_status onlineStatus,td.current_reading currentReading," +
            "    td.device_type deviceType,td.power_status powerStatus,  " +
            "    td.create_time createTime,td.report_time reportTime,  " +
            "    td.device_third_no deviceThirdNo,  " +
            "    td.third_gate_no thirdGateNo,  " +
            "    td.creator createUser,  " +
            "    td.version  " +
            "    from tb_device td  " +
            "    WHERE td.device_no=#{deviceNo};")
    WaterMeterCommonResponseModel getWatermeterInfoBydeviceNo(@Param("deviceNo") String deviceNo);

    /**
     * 根据索引进行更新
     * @param currentDevice 含有待更新的值与索引条件，索引条件必须不含有索引会报错
     * @return 受影响的行数
     */
    int updateByIndex(TbDeviceDo currentDevice);

}
